Example 7-1
-------------------------------------------------------------------------------------------------------------------------------------------------
Create Proc myPro
		@No int,
		@Result float output
	As
		Declare @total int, @j int	
		Declare @str varchar(50)
		Set @j=10
		Set @j=@j+(select count(*) from tblCustomers)
		Select @j=@j+10
		Select @j=@j+ count(*),
		@total=sum(Amount) from tblInvoiceDetails
		Where ItemID=@ID
		print @j
		print @total
GO

Example 7-2
-------------------------------------------------------------------------------------------------------------------------------------------------
Create Proc myPro
		@No int,
		@Result float output
	As
		Declare @total int, @j int	
		Declare @str varchar(50)
		Set @j=10
		Set @j=@j+(select count(*) from tblCustomers)
		Select @j=@j+10
		Select @j=@j+ count(*),
		@total=sum(Amount) from tblInvoiceDetails
		Where ItemID=@No		
		Set @Result=@total/2
		Return @total

Example 7-3
-------------------------------------------------------------------------------------------------------------------------------------------------
Execute mypro 10,0
Exec myPro 10,30

Example 7-4
-------------------------------------------------------------------------------------------------------------------------------------------------
declare @output float
exec myPro 1,@output output
print @output
GO
Example 7-5
-------------------------------------------------------------------------------------------------------------------------------------------------
declare @output float
declare @getresult int
exec @getresult=myPro 2,@output output
print @output
print @getresult
GO

Example 7-6
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROC yourPro
	AS
		declare @output float
		declare @getresult int
		exec myPro 2,@output output
		exec @getresult=myPro 2,@output output
		print @output
		print @getresult
Example 7-7
-------------------------------------------------------------------------------------------------------------------------------------------------
alter Proc myPro
		@No int,
		@Result float output
	As
		Declare @total int, @j int	
		Declare @str varchar(50)
		Set @j=10
		Set @j=@j+(select count(*) from 
		tblCustomers)
		Select @j=@j+10
		Select @j=@j+ count(*),
		@total=sum(Amount) from 
		tblInvoiceDetails
		Where ItemID=@No	
		set @Result=@total/2
		return @total
		
	GO

Example 7-8
-------------------------------------------------------------------------------------------------------------------------------------------------
alter Proc myPro
		@No int,
		@Result float output
		With Recompile
	As
		Declare @total int, @j int	
		Declare @str varchar(50)
		Set @j=10
		Set @j=@j+(select count(*) from 
		tblCustomers)
		Select @j=@j+10
		Select @j=@j+ count(*),
		@total=sum(Amount) from 
		tblInvoiceDetails
		Where ItemID=@No	
		set @Result=@total/2
		return @total
		
	GO

Example 7-9
-------------------------------------------------------------------------------------------------------------------------------------------------
sp_recompile myPro

Example 7-10
-------------------------------------------------------------------------------------------------------------------------------------------------
Exec myPro With Recompile

Example 7-11
-------------------------------------------------------------------------------------------------------------------------------------------------
Drop Proc  myPro 

Example 7-12
-------------------------------------------------------------------------------------------------------------------------------------------------
SELECT *, dbo.MyFunction()
	FROM MyTable


Example 7-13
-------------------------------------------------------------------------------------------------------------------------------------------------
SELECT *
	FROM MyTableFunction()


Example 7-14
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION getAmount (@ItemID int)  
RETURNS float
AS  
BEGIN 
	Return(select sum(Amount) 
	from tblInvoiceDetails 
	Where ItemID=@ItemID) 
END


Example 7-16
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION CubicVolume
	(@CubeLength decimal(4,1), 
	@CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3)
AS
BEGIN
   RETURN(@CubeLength * @CubeWidth * @CubeHeight)
END

GO
Example 7-17
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Bricks
   (
    BrickPartNmbr   int PRIMARY KEY,
    BrickColor      nchar(20),
    BrickHeight     decimal(4,1),
    BrickLength     decimal(4,1),
    BrickWidth      decimal(4,1),
    BrickVolume AS
              (
               dbo.CubicVolume(BrickHeight,
                         BrickLength, BrickWidth)
              )
   )

GO
Example 7-18
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION getTable
	(@SATRTID int,@ENDID int)
RETURNS table
AS
   RETURN(select * from tblInvoiceDetails 
	where ItemID between @SATRTID and @ENDID)
GO
Example 7-19
-------------------------------------------------------------------------------------------------------------------------------------------------
select * from getTable(1,4)
where OrderNo=1
GO
Example 7-20
-------------------------------------------------------------------------------------------------------------------------------------------------
select * from getTable(1,4)
where OrderNo=1

Example 7-21
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE RULE SpecialCity
	AS 
	@SpecialCity IN 
	('HCM','HAN','DAN', 'BDU','DON')


Example 7-22
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DEFAULT Price 
    AS 1000


GO
Example 7-23
-------------------------------------------------------------------------------------------------------------------------------------------------
sp_bindefault Price, 'tblInvoiceDetails.Price'

